import configparser
import datetime

from pandas import DataFrame
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

from bean.PrivateFundManagerClassificationInfo import PrivateFundManagerClassificationInfo
from bean.PrivateFundManagerClassificationInfo import create_table as c_t
from bean.PrivateFundManagerClassificationInfo_urls import PrivateFundManagerClassificationInfo_urls

# 创建ConfigParser对象
config = configparser.ConfigParser()

# 读取配置文件
config.read("../database_config.ini")

# 获取数据库连接配置信息
block = 'Database_test'  # 开发环境
# block = 'Database'      # 生产环境

host = config.get(block, 'host')
port = config.get(block, 'port')
username = config.get(block, 'username')
password = config.get(block, 'password')
db_name = config.get(block, 'database')

# 获取数据库实例
db = None
try:
    db = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{db_name}?charset=utf8')
    # 其他操作
except SQLAlchemyError as e:
    print("数据库连接错误:", e)

# 建立映射关系
Base = declarative_base()
# 创建会话
Session = sessionmaker(db)
# 打开会话
session = Session()


# 创建表
def create_table():
    c_t(db)


def insert(data: list):
    # 创建会话
    obj_session = sessionmaker(db)
    # 打开会话
    db_session = obj_session()
    # 向表中添加数据,此时数据保存在内存中
    db_session.add_all(data)
    # 提交数据，将数据保存到数据库中
    db_session.commit()
    # 关闭会话
    db_session.close()
    print('插入数据成功！')


# 示例 1
sample_1 = PrivateFundManagerClassificationInfo(
    number=1,
    manager_name='ABC Capital Management',
    legal_representative='John Doe',
    institution_type='Limited Partnership',
    registration_number='REG12345',
    registered_location='New York',
    office_location='Manhattan',
    establishment_date=datetime.datetime(2010, 5, 15),
    registration_date=datetime.datetime(2010, 6, 1),
    fund_count=20,
    member_type='Gold',
    has_information_notice=True,
    has_integrity_information=True
)

# 示例 2
sample_2 = PrivateFundManagerClassificationInfo(
    number=2,
    manager_name='XYZ Investment Group',
    legal_representative='Jane Smith',
    institution_type='Corporation',
    registration_number='REG67890',
    registered_location='California',
    office_location='San Francisco',
    establishment_date=datetime.datetime(2005, 10, 20),
    registration_date=datetime.datetime(2005, 11, 5),
    fund_count=15,
    member_type='Silver',
    has_information_notice=True,
    has_integrity_information=False
)

# 示例 3
sample_3 = PrivateFundManagerClassificationInfo(
    number=3,
    manager_name='PQR Hedge Fund',
    legal_representative='David Johnson',
    institution_type='Limited Liability Company',
    registration_number='REG54321',
    registered_location='Texas',
    office_location='Dallas',
    establishment_date=datetime.datetime(2018, 3, 10),
    registration_date=datetime.datetime(2018, 4, 1),
    fund_count=30,
    member_type='Platinum',
    has_information_notice=False,
    has_integrity_information=True
)

data = [sample_1, sample_2, sample_3]


def insert_data_to_db(df: DataFrame, current_page_num: str, logging):
    logging.info(f"正在写入第{current_page_num}页...")  # 记录日志信息
    for index, row in df.iterrows():
        # 创建 PrivateFundManagerClassificationInfo 对象
        private_fund_manager = PrivateFundManagerClassificationInfo(
            number=row['编号'],
            manager_name=row['私募基金管理人名称'],
            legal_representative=row['法定代表人/执行事务合伙人(委派代表)姓名'],
            institution_type=row['机构类型'],
            registration_number=row['登记编号'],
            registered_location=row['注册地'],
            office_location=row['办公地'],
            establishment_date=row['成立时间'],
            registration_date=row['登记时间'],
            fund_count=row['在管基金数量'],
            member_type=row['会员类型'],
            has_information_notice=row['是否有提示信息'],
            has_integrity_information=row['是否有诚信信息']
        )

        # 将对象添加到数据库会话中
        session.add(private_fund_manager)

    try:
        # 提交会话以实际写入数据库
        session.commit()
        logging.info("写入成功！")
        print("写入成功！")
        logging.info(
            f"私募基金管理人名称:{row['私募基金管理人名称']},法定代表人:{row['法定代表人/执行事务合伙人(委派代表)姓名']}")  # 记录日志信息

        session.close()
    except Exception as error:
        logging.error(f"写入数据库时出现错误：{error}")  # 记录错误信息
        raise error


def close_session():
    # 关闭会话
    session.close()


def insert_data_to_db_urls(title, link, current_page_num: str, logging):
    logging.info(f"正在写入第{current_page_num}页...")  # 记录日志信息

    p = PrivateFundManagerClassificationInfo_urls(title, link)

    # 将对象添加到数据库会话中
    session.add(p)
    logging.info(
        f"私募基金管理人名称:{title},链接:{link}")  # 记录日志信息

    try:
        # 提交会话以实际写入数据库
        session.commit()
        logging.info("写入成功！")
        session.close()
    except Exception as error:
        logging.error(f"写入数据库时出现错误：{error}")  # 记录错误信息
        raise error
